# encoding: utf-8

import requests
import json
import datetime
from xlrd import xldate_as_tuple
import os
import xlrd
from urllib.parse import quote
from widget.info_box import *
from widget.utils.my_threading_part import MyThreadFunc
# from widget.daoru_page.import_init_page.sqlserver_to_essystem_table__spical_source_init_window import SpicalSourceInitWindow
from widget.db_init.sqlUtils.sqlserverUtils import SqlServerOp
from widget.utils.utils import MyUtils
import time
from widget.daoru_page.import_init_page.import_func.base_func import BaseImportFunc, ProgressSignal, ImportProgressBox


class Excl2Es(BaseImportFunc):
    '''
    sqlserver导入es的方法
    初始化的时候传入 父控件，来源信息字典，目标信息字典
    '''
    def __init__(self, source_dict, target_dict):
        super(Excl2Es, self).__init__(source_dict, target_dict)

    def get_all_data(self, excl_path, sheet_index, operate_dict, related_field_dict=None):
        '''
        读取excl中的数据
        :return:
        '''

        # 打开excel表格
        workbook = xlrd.open_workbook(excl_path)
        # 获取第一个sheet对象
        sheet1_object = workbook.sheet_by_index(sheet_index)
        all_data_count = sheet1_object.nrows

        all_data = []

        # 不是单独导入附件
        if not related_field_dict:
            for i in range(all_data_count):
                if i == 0:
                    continue
                data = []
                for k, name in enumerate(self.source_dict['com']['field_name_list']):
                    for operate in operate_dict['operate_list']:
                        if name == operate['source_code']:

                            # 判断ctype是否==3，如果是3就转成日期型字段 start -----------------------
                            cell_field = sheet1_object.cell(i, k)
                            if cell_field.ctype == 3:
                                # 转成datetime对象
                                date = datetime.datetime(*xldate_as_tuple(cell_field.value, 0))
                                cell_value = date.strftime('%Y-%m-%d %H:%M:%S')
                            else:
                                cell_value = str(cell_field.value)
                            # 判断ctype是否==3，如果是3就转成日期型字段 end -------------------------

                            data.append(cell_value)
                all_data.append(data)
        # 是单独导入附件
        else:
            for i in range(all_data_count):
                if i == 0:
                    continue
                data = []
                # 插入附件字段
                for k, name in enumerate(self.source_dict['com']['field_name_list']):
                    for operate in operate_dict['operate_list']:
                        if name == operate['source_code']:

                            # 判断ctype是否==3，如果是3就转成日期型字段 start -----------------------
                            cell_field = sheet1_object.cell(i, k)
                            if cell_field.ctype == 3:
                                # 转成datetime对象
                                date = datetime.datetime(*xldate_as_tuple(cell_field.value, 0))
                                cell_value = date.strftime('%Y-%m-%d %H:%M:%S')
                            else:
                                cell_value = str(cell_field.value)
                            # 判断ctype是否==3，如果是3就转成日期型字段 end -------------------------

                            data.append(cell_value)
                # 插入附件与资料的关联字段
                for k, name in enumerate(self.source_dict['com']['field_name_list']):
                    for key in related_field_dict:
                        if name == key:

                            # 判断ctype是否==3，如果是3就转成日期型字段 start -----------------------
                            cell_field = sheet1_object.cell(i, k)
                            if cell_field.ctype == 3:
                                # 转成datetime对象
                                date = datetime.datetime(*xldate_as_tuple(cell_field.value, 0))
                                cell_value = date.strftime('%Y-%m-%d %H:%M:%S')
                            else:
                                cell_value = str(cell_field.value)
                            # 判断ctype是否==3，如果是3就转成日期型字段 end -------------------------

                            data.append(cell_value)
                all_data.append(data)

        # print(all_data)
        return all_data
        # print(1/0)

        #     col_slice = sheet1_object.row_values(rowx=i)
        #     if i == 0:
        #         self.excl_source_dict['com']['field_name_list'] = col_slice
        #     else:
        #         self.excl_source_dict['com']['field_content_list'].append(col_slice)
        # print(self.excl_source_dict)
        # # excl表的前十条数据挂载到qApp中
        # qApp.cx_src = self.excl_source_dict

    # # 导入资料方法 start -------------------------------------------------------------------------
    # def start_import_ziliao(self, all_data, RUID, operate_dict, table_parent_widget):
    #     '''
    #     开始导入(资料部分)
    #     :return:
    #     '''
    #     print('加载导入数据...')
    #
    #     # all_data = self.get_all_data(qApp.cx_src['uni']['file_path'], sheet_index, operate_dict)
    #
    #     # print(len(all_data))
    #     # print('all_data: {0}'.format(all_data))
    #     # print(1/0)
    #
    #     # 消息盒子start -------------------------------------------
    #     table_parent_widget.progress_box = ImportProgressBox(my_parent=table_parent_widget, sum_num=len(all_data))
    #
    #     # self.table1.parent().progress_box.setWindowFlag(Qt.WindowStaysOnTopHint)
    #     # def stop_run():
    #     #     self.table1.parent().run_flag = False
    #     # 关闭按钮点击，终止线程
    #     # self.table1.parent().progress_box.close_btn.clicked.connect(stop_run)
    #     # 取消按钮点击，关闭窗口
    #     table_parent_widget.progress_box.show()
    #
    #     # 初始化进度条信号实例
    #     self.sig = ProgressSignal()
    #     # 绑定更新进度槽
    #     self.sig.dataChanged.connect(table_parent_widget.progress_box.update_progress)
    #     # 绑定导入结束槽
    #     self.sig.dataFinished.connect(table_parent_widget.progress_box.import_finished)
    #
    #     # 消息盒子end --------------------------------------------
    #
    #     # 展示进度
    #     def start_run():
    #         data_dict_list = []
    #         for i, field in enumerate(all_data):
    #             field = list(field)    # 元祖转字典
    #             data_dict = {}
    #
    #             # 插入一对一元数据 start -----------------------------------------------
    #             for k, item in enumerate(operate_dict['operate_list']):
    #                 # if k != 10100001:
    #                 if not field[k]:
    #                     # print('field[k]: {0}-{1}'.format(k, field[k]))
    #                     if item['target_code'] != '10100001':
    #                         continue
    #                     else:
    #                         field[k] = ' '
    #                         # print(1/0)
    #                 data_dict[item['target_code']] = field[k]
    #
    #             data_dict_list.append(data_dict)
    #             print('data_dict: {0}'.format(data_dict))
    #
    #             if (i+1) % self.speed == 0 or (i+1) == len(all_data):
    #                 '''
    #                 {
    #                     "RUID": "任务唯一标识",
    #                     "data": [
    #                       {"RUID":"","10100001":"test","1590001":["1","2"]}
    #                     ]
    #                 }
    #                 '''
    #                 data_dict_encoding = quote(json.dumps(data_dict_list, ensure_ascii=False))
    #                 # print('data_dict_encoding: {0}'.format(data_dict_encoding))
    #
    #                 # 导入数据格式 start --------------------------------
    #                 import_dict = {
    #                     "RUID": RUID,
    #                     "data": data_dict_encoding,
    #                     "appendix": quote(json.dumps([], ensure_ascii=False))
    #                     # "data": [data_dict]
    #                 }
    #                 # 导入数据格式 end --------------------------------
    #
    #                 # 进度(第一次/重新传递数据传递start,最后一批数据传递finish，中间不传递)
    #                 if (i + 1) == self.speed:
    #                     import_dict['schedule'] = 'start'
    #                 if (i + 1) == len(all_data):
    #                     import_dict['schedule'] = 'finish'
    #
    #                 print('import_dict: {0}'.format(import_dict))
    #
    #                 # url = qApp.cx_es_db_ip + '/import/importdata'
    #                 # url = qApp.cx_es_db_ip + '/import/importdataorappendix'
    #                 url = qApp.cx_es_db_ip + '/dataprocessingapi/dataimport/importdataorappendix.action'
    #                 print('请求导入。。。')
    #                 print('当前任务RUID:{0}'.format(RUID))
    #                 res = requests.post(url, data=import_dict, verify=False)
    #                 # print('请求返回。。。')
    #                 print('后台返回值：{0}'.format(res.status_code))
    #                 print('当前进度： {0}'.format(i))
    #
    #                 if res.status_code == 500:
    #                     print('报错信息{0}：'.format(res.text))
    #                     return
    #                     # return_dict = {'state': 500}
    #                     # return return_dict
    #                 field_dict = json.loads(res.text) if res.text else None
    #                 # print(field_dict)
    #                 # 请求结果状态
    #                 state = field_dict['cxajaxrc']
    #                 print('state: {0}'.format(state))
    #                 if state == '-1':
    #                     self.add_err_log('资料进度：{0}；请求成功，返回-1，报错信息：{1}'.format(i, field_dict[
    #                         'cxajaxerrmsg'] if 'cxajaxerrmsg' in field_dict else ''))
    #                     print('请求成功，返回错误：{0}'.format(field_dict['cxajaxerrmsg']))
    #                 if state == '0':
    #                     print('field_dict: {0}'.format(field_dict))
    #                     # returnvalue = field_dict['returnvalue']
    #                     # if 'cxajaxloglist' in returnvalue:
    #                     #     self.add_err_log('资料进度：{0}；请求成功，返回0，报错信息：{1}'.format(i, str(
    #                     #         returnvalue['cxajaxloglist'] if 'cxajaxloglist' in returnvalue else '')))
    #
    #                     # 插入日志 start -----------------------------------------------------
    #                     try:
    #                         returnvalue = field_dict['returnvalue']
    #                         if 'cxajaxloglist' in returnvalue:
    #                             log_str = ''
    #                             for u in range(len(returnvalue['cxajaxloglist'])):
    #                                 cur_num = (i + 1) - 25 + (u + 1)
    #                                 log_str += '资料进度：{0}-{1}；请求成功，返回0，导入信息：{2}\n'.format(
    #                                     cur_num,
    #                                     data_dict_list[u]['10100001'],
    #                                     str(returnvalue['cxajaxloglist'][u] if returnvalue['cxajaxloglist'][u] else '导入成功')
    #                                 )
    #                             self.add_err_log(log_str)
    #                     except Exception as e:
    #                         print(e)
    #                     # 插入日志 end -----------------------------------------------------
    #
    #                     print('请求成功，返回正确')
    #                     # self.add_err_log('资料进度：{0}；请求成功，返回0，报错信息：{1}'.format(i, str(field_dict['cxajaxloglist'] if 'cxajaxloglist' in field_dict else '')))
    #                     # print('请求成功，返回正确')
    #                     # print('data_dict_encoding: {0}'.format(data_dict_encoding))
    #                     # print('data_dict: {0}'.format(json.dumps(data_dict_list, ensure_ascii=False)))
    #                     # print(111111111111111111111111111111111111111111)
    #                     success_num = field_dict['returnvalue']["success"]
    #                     fail_num = field_dict['returnvalue']["error"]
    #                     # cal_signal = pyqtSignal(np.ndarray, np.ndarray)
    #
    #                     # self.download_proess_signal.emit(int(proess))
    #
    #                     # 发射进度条更新信号
    #                     self.sig.dataChanged.emit(success_num, fail_num, i+1)
    #
    #                     # self.table1.parent().progress_box.update_progress(success_num, fail_num)
    #
    #                     # print('成功{0}条'.format(success_num))
    #                     # print('失败{0}条'.format(fail_num))
    #                     # return_dict = {'state': -1, 'success_num': success_num, 'fail_num': fail_num}
    #                     # return return_dict
    #
    #                 # 一百天传完后，清空队列，再传下一百条
    #                 data_dict_list = []
    #
    #         # 发射导入结束信号
    #         self.sig.dataFinished.emit(True)
    #     # _thread.start_new_thread(start_run, ())
    #
    #     self.import_thread = MyThreadFunc(start_run)
    #     self.import_thread.start()
    #     # 关闭按钮点击，终止线程
    #     table_parent_widget.progress_box.close_btn.clicked.connect(self.import_thread.stop)
    #     table_parent_widget.progress_box.cancel_btn.clicked.connect(table_parent_widget.progress_box.close_btn.click)
    # # 导入资料方法 end -------------------------------------------------------------------------
    #
    # # 导入附件方法 start -----------------------------------------------------------------------
    # def start_import_fujian(self, all_data, RUID, operate_dict, table_parent_widget, related_field_dict):
    #     '''
    #     开始导入(附件单独方法)
    #     :return:
    #     '''
    #     # # 获取路径ruid -----------------------
    #     # book_code = 1987051001
    #     # url = qApp.cx_es_db_ip + '/import/metadatalist?datamodel={0}'.format(book_code)
    #     # print(url)
    #     # res = requests.get(url, verify=False)
    #     # # print(res.text)
    #     # field_dict = json.loads(res.text) if res.text else None
    #     # re_path_ruid = [field['vdir'] for field in field_dict['metadata'] if field['code'] == 350050001][0]
    #     # print('路径ruid: {0}'.format(re_path_ruid))
    #     # # 获取路径ruid -----------------------
    #
    #     print('加载导入数据...')
    #     # pf_code = {
    #     #     'p_ruid': 'RUID',
    #     #     'f_ruid': 'doruid',
    #     # }
    #     # print('{0} {1} {2} {3} {4}'.format(self.source_dict['server_name'], self.source_dict['port'], self.source_dict['db_name'], self.source_dict['username'], self.source_dict['password']))
    #     # sql = """SELECT {0}, {1} FROM {2} WHERE f_ruid='jmu_sqlserver_no1_cjg2cxstar_cjg_book_72000002'""".format(
    #     #     ','.join([source_field['source_code'] for source_field in self.operate_dict['operate_list']]),
    #     #     ','.join(qApp.cx_source_sqlserver_dict['field_pf_name_list']),
    #     #     self.source_dict['table_name']
    #     # )
    #
    #     # all_data = self.get_all_data(self.source_dict['uni']['file_path'], sheet_index, operate_dict, related_field_dict)
    #
    #     print('all_data: {0}'.format(all_data))
    #     # print('all_data: {0}'.format(all_data))
    #     # print(1/0)
    #
    #     # 消息盒子start -------------------------------------------
    #     table_parent_widget.progress_box = ImportProgressBox(my_parent=table_parent_widget, sum_num=len(all_data))
    #
    #     # self.table1.parent().progress_box.setWindowFlag(Qt.WindowStaysOnTopHint)
    #     # def stop_run():
    #     #     self.table1.parent().run_flag = False
    #     # 关闭按钮点击，终止线程
    #     # self.table1.parent().progress_box.close_btn.clicked.connect(stop_run)
    #     # 取消按钮点击，关闭窗口
    #     table_parent_widget.progress_box.show()
    #
    #     # 初始化进度条信号实例
    #     self.sig = ProgressSignal()
    #     # 绑定更新进度槽
    #     self.sig.dataChanged.connect(table_parent_widget.progress_box.update_progress)
    #     # 绑定导入结束槽
    #     self.sig.dataFinished.connect(table_parent_widget.progress_box.import_finished)
    #
    #     # 消息盒子end --------------------------------------------
    #
    #     # 展示进度
    #     def start_run():
    #         data_dict_list = []
    #         for i, field in enumerate(all_data):
    #             # 如果运行flag为False 就跳出
    #             # if not self.table1.parent().run_flag:
    #             #     print('线程终止')
    #             #     return
    #
    #             data_dict = {
    #                 "field": {},
    #                 "relateddatatype": self.target_dict['target_table_code'],
    #                 "relatedfield": {}
    #             }
    #             # 插入一对一元数据 start -----------------------------------------------
    #             for k, item in enumerate(operate_dict['operate_list']):
    #                 if not field[k]:
    #                     continue
    #                 data_dict["field"][item['target_code']] = field[k]
    #
    #             # 插入附件与资料的关联字段
    #             for j, key in enumerate(related_field_dict):
    #                 data_dict["relatedfield"][related_field_dict[key]] = field[len(field) - len(related_field_dict) + j]
    #
    #             # 插入固定值 start -------------------------------------------------
    #             # data_dict['wfstatus'] = 'A'
    #             # # data_dict['7'] = 'A'
    #             # data_dict['customercode'] = '1878'
    #             # data_dict['siteid'] = 'urmp'
    #             # # data_dict['20100001'] = 'publicpost'    # 保密级别
    #             # data_dict['mdcode'] = '350050001'
    #             # data_dict['vdir'] = re_path_ruid
    #
    #             # 附件的特殊处理 start --------------------------------------------
    #             # 判断，如果 data_dict字典中没有 ext键，那么将 filename通过 “.” 拆分成 filename 和 ext
    #             if not 'ext' in data_dict['field'] and '.' in data_dict['field']['filename']:
    #                 # 文件名截取
    #                 old_filename = data_dict['field']['filename']
    #                 data_dict['field']['filename'] = old_filename.split('.')[0]
    #                 data_dict['field']['ext'] = old_filename.split('.')[1]
    #             if 'ext' in data_dict['field'] and 'name' in data_dict['field'] and '.' in data_dict['field']['name']:
    #                 # 显示文件名截取
    #                 old_name = data_dict['field']['name']
    #                 data_dict['field']['name'] = old_name.split('.')[0]
    #             # path 的值赋给 path0
    #             if data_dict['field']['path']:
    #                 data_dict['field']['path0'] = data_dict['field']['path']
    #             # 附件的特殊处理 start --------------------------------------------
    #
    #             # 插入固定值 end -------------------------------------------------
    #
    #             data_dict_list.append(data_dict)
    #             # print('data_dict: {0}'.format(data_dict))
    #             # print(1 / 0) ------------
    #
    #             # 每过 self.speed 条数据发送一次请求
    #             # self.add_err_log('附件进度：{0}'.format(i))
    #             # print(i)
    #             if (i + 1) % self.speed == 0 or (i + 1) == len(all_data):
    #                 '''
    #                 {
    #                     "RUID": "任务唯一标识",
    #                     "data": [
    #                       {"RUID":"","10100001":"test","1590001":["1","2"]}
    #                     ]
    #                 }
    #                 '''
    #                 # data_dict = [{"RUID": "", "10100001": "test", "1590001": ["1", "2"]}]
    #                 print('data_dict_list: {0}'.format(data_dict_list))
    #                 data_dict_encoding = quote(json.dumps(data_dict_list, ensure_ascii=False))
    #                 # import_dict = {
    #                 #     # "RUID": self.task_dict['RUID'],
    #                 #     "data": data_dict_encoding
    #                 #     # "data": [data_dict]
    #                 # }
    #
    #                 # 导入数据格式 start --------------------------------
    #                 import_dict = {
    #                     "RUID": RUID,
    #                     "data": [],
    #                     "appendix": data_dict_encoding
    #                     # "data": [data_dict]
    #                 }
    #                 # 导入数据格式 end --------------------------------
    #
    #                 # 进度(第一次/重新传递数据传递start,最后一批数据传递finish，中间不传递)
    #                 if (i + 1) == self.speed:
    #                     import_dict['schedule'] = 'start'
    #                 if (i + 1) == len(all_data):
    #                     import_dict['schedule'] = 'finish'
    #
    #                 # print({"data": data_dict_list})
    #                 # return
    #                 # print(1/0)
    #
    #                 print('import_dict: {0}'.format(import_dict))
    #
    #                 # url = qApp.cx_es_db_ip + '/import/importdofile6'
    #                 # url = qApp.cx_es_db_ip + '/import/importdataorappendix'
    #                 url = qApp.cx_es_db_ip + '/dataprocessingapi/dataimport/importdataorappendix.action'
    #                 # print(2211111)
    #                 res = requests.post(url, data=import_dict, verify=False)
    #                 # print(2222222)
    #                 print(res)
    #                 field_dict = json.loads(res.text) if res.text else None
    #                 print('返回内容： {0}'.format(field_dict))
    #                 if not field_dict:
    #                     print("服务器估计挂了")
    #                     # return_dict = {'state': 500}
    #                     # return return_dict
    #                 # 请求结果状态
    #                 state = field_dict['cxajaxrc']
    #                 print('state: {0}'.format(state))
    #                 if state == '-1':
    #                     self.add_err_log('附件进度：{0}；请求成功，返回-1，报错信息：{1}'.format(i, field_dict[
    #                         'cxajaxerrmsg'] if 'cxajaxerrmsg' in field_dict else ''))
    #                     print('请求成功，返回错误：{0}'.format(field_dict['cxajaxerrmsg']))
    #                 if state == '0':
    #                     # returnvalue = field_dict['returnvalue']
    #                     # if 'cxajaxloglist' in returnvalue:
    #                     #     self.add_err_log('附件进度：{0}；请求成功，返回0，报错信息：{1}'.format(i, str(
    #                     #         returnvalue['cxajaxloglist'] if 'cxajaxloglist' in returnvalue else '')))
    #
    #                     # 插入日志 start -----------------------------------------------------
    #                     try:
    #                         returnvalue = field_dict['returnvalue']
    #                         if 'cxajaxloglist' in returnvalue:
    #                             log_str = ''
    #                             for u in range(len(returnvalue['cxajaxloglist'])):
    #                                 cur_num = (i + 1) - 25 + (u + 1)
    #                                 log_str += '资料进度：{0}；请求成功，返回0，导入信息：{1}\n'.format(
    #                                     cur_num,
    #                                     # data_dict_list[u]['10100001'],
    #                                     str(returnvalue['cxajaxloglist'][u] if returnvalue['cxajaxloglist'][u] else '导入成功')
    #                                 )
    #                             self.add_err_log(log_str)
    #                     except Exception as e:
    #                         print(e)
    #                     # 插入日志 end -----------------------------------------------------
    #
    #                     print('请求成功，返回正确')
    #                     # self.add_err_log('资料进度：{0}；请求成功，返回0，报错信息：{1}'.format(i, str(field_dict['cxajaxloglist'] if 'cxajaxloglist' in field_dict else '')))
    #                     # print('请求成功，返回正确')
    #                     success_num = field_dict['returnvalue']["dofile6successNum"]
    #                     fail_num = field_dict['returnvalue']["dofile6errorNum"]
    #                     # cal_signal = pyqtSignal(np.ndarray, np.ndarray)
    #
    #                     # self.download_proess_signal.emit(int(proess))
    #
    #                     # 发射进度条更新信号
    #                     self.sig.dataChanged.emit(success_num, fail_num, i + 1)
    #
    #                     # self.table1.parent().progress_box.update_progress(success_num, fail_num)
    #
    #                     # print('成功{0}条'.format(success_num))
    #                     # print('失败{0}条'.format(fail_num))
    #                     # return_dict = {'state': -1, 'success_num': success_num, 'fail_num': fail_num}
    #                     # return return_dict
    #
    #                 # 一百天传完后，清空队列，再传下一百条
    #                 data_dict_list = []
    #
    #         # 发射导入结束信号
    #         self.sig.dataFinished.emit(True)
    #
    #     # _thread.start_new_thread(start_run, ())
    #
    #     self.import_thread = MyThreadFunc(start_run)
    #     self.import_thread.start()
    #     # 关闭按钮点击，终止线程
    #     table_parent_widget.progress_box.close_btn.clicked.connect(self.import_thread.stop)
    #     table_parent_widget.progress_box.cancel_btn.clicked.connect(table_parent_widget.progress_box.close_btn.click)
    # # 导入附件方法 end -----------------------------------------------------------------------


